package com.mr.web.controller;

import java.io.IOException;
import java.io.Writer;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.mr.util.DateUtil;
import com.mr.util.StringUtil;

@Controller
public class SysAdminController extends AnnotationController {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate template) {
		jdbcTemplate = template;
	}

	@RequestMapping(value = "/admin/sysmgr/execQuery.xhtml", method = RequestMethod.GET)
	public String query() {
		return "admin/sysmgr/execQuery.vm";
	}

	@RequestMapping(value = "/admin/sysmgr/execQuery.xhtml", method = RequestMethod.POST)
	public String query(HttpServletResponse res, String sql, String contentType,
			String queryAll, ModelMap model) throws IOException {
		SqlRowSet result = null;
		try {
			result = jdbcTemplate.queryForRowSet(sql);
		} catch (Exception e) {
			return forwadMessage(model, StringUtil.getExceptionTrace(e));
		}
		if (StringUtils.contains(contentType, "xls")) {
			res.setContentType("application/xls");
			res.addHeader("Content-Disposition", "attachment;filename=execQuery"
					+ DateUtil.format(new Date(), "yyMMdd_hhmmss") + ".xls");
		} else {
			res.setContentType("text/html");
		}
		Writer writer = res.getWriter();
		String header = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\""
				+ "\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">"
				+ "<html xmlns=\"http://www.w3.org/1999/xhtml\">"
				+ "<head>"
				+ "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"/>"
				+ "<link rel=\"stylesheet\" type=\"text/css\" href=\"http://static.gewara.cn/component/css/default.css\" />"
				+ "</head><body>";
		writer.write(header);
		writer.write("<table class=\"table\"><tr>");
		SqlRowSetMetaData md = result.getMetaData();
		for (int i = 1, count = md.getColumnCount(); i <= count; i++) {
			writer.write("<td>" + result.getMetaData().getColumnName(i) + "</td>");
		}
		writer.write("</tr>");
		int max = 20;
		if (StringUtils.isNotBlank(queryAll))
			max = 1000;
		int rowcount = 0;
		while (result.next() && rowcount < max) {
			writer.write("<tr>");
			for (int i = 1, count = md.getColumnCount(); i <= count; i++) {
				Object v = result.getObject(i);
				writer.write("<td>" + v + "</td>");
			}
			writer.write("</tr>");
			rowcount++;
		}
		writer.write("</table></body></html>");
		return null;
	}
}
